﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace Vit.SchemaExplorer.Test
{
    /// <summary>
    /// Summary description for UnitTest1
    /// </summary>
    [TestClass]
    public class DatabaseSchemaTest
    {
        public DatabaseSchemaTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        [ClassCleanup()]
        public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        [TestInitialize()]
        public void MyTestInitialize() 
        { 
            dbs = DatabaseSchemaFactory.Create("SchemExplorerTest");
            dbs.Refresh();
        }
        //
        // Use TestCleanup to run code after each test has run
        [TestCleanup()]
        public void MyTestCleanup() 
        { 
        }
        //
        #endregion

        private DatabaseSchema dbs;

        [TestMethod]
        public void TestColumnTypeMapping()
        {
            var tbl = dbs.GetTable("dbo", "AllTypes");
            Assert.IsNotNull(tbl);
            Assert.AreEqual<int>(34, tbl.Columns.Count);
            Assert.AreEqual<Type>(typeof(Guid), tbl.Columns[0].SystemType);
            Assert.AreEqual<Type>(typeof(long?), tbl.Columns[1].SystemType);
            Assert.AreEqual<Type>(typeof(Binary), tbl.Columns[2].SystemType);
            Assert.AreEqual<Type>(typeof(bool?), tbl.Columns[3].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[4].SystemType);
            Assert.AreEqual<Type>(typeof(DateTime?), tbl.Columns[5].SystemType);
            Assert.AreEqual<Type>(typeof(DateTime?), tbl.Columns[6].SystemType);
            Assert.AreEqual<Type>(typeof(DateTime?), tbl.Columns[7].SystemType);
            Assert.AreEqual<Type>(typeof(DateTimeOffset?), tbl.Columns[8].SystemType);
            Assert.AreEqual<Type>(typeof(decimal?), tbl.Columns[9].SystemType);
            Assert.AreEqual<Type>(typeof(double?), tbl.Columns[10].SystemType);
            Assert.AreEqual<Type>(typeof(Binary), tbl.Columns[11].SystemType);
            Assert.AreEqual<Type>(typeof(int?), tbl.Columns[12].SystemType);
            Assert.AreEqual<Type>(typeof(decimal?), tbl.Columns[13].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[14].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[15].SystemType);
            Assert.AreEqual<Type>(typeof(decimal?), tbl.Columns[16].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[17].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[18].SystemType);
            Assert.AreEqual<Type>(typeof(float?), tbl.Columns[19].SystemType);
            Assert.AreEqual<Type>(typeof(DateTime?), tbl.Columns[20].SystemType);
            Assert.AreEqual<Type>(typeof(short?), tbl.Columns[21].SystemType);
            Assert.AreEqual<Type>(typeof(decimal?), tbl.Columns[22].SystemType);
            Assert.AreEqual<Type>(typeof(object), tbl.Columns[23].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[24].SystemType);
            Assert.AreEqual<Type>(typeof(TimeSpan?), tbl.Columns[25].SystemType);
            Assert.AreEqual<Type>(typeof(Binary), tbl.Columns[26].SystemType);
            Assert.AreEqual<Type>(typeof(byte?), tbl.Columns[27].SystemType);
            Assert.AreEqual<Type>(typeof(Guid?), tbl.Columns[28].SystemType);
            Assert.AreEqual<Type>(typeof(Binary), tbl.Columns[29].SystemType);
            Assert.AreEqual<Type>(typeof(Binary), tbl.Columns[30].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[31].SystemType);
            Assert.AreEqual<Type>(typeof(string), tbl.Columns[32].SystemType);
            Assert.AreEqual<Type>(typeof(System.Xml.Linq.XElement), tbl.Columns[33].SystemType);
        }

        [TestMethod]
        public void TestKeys()
        {
            var p1 = dbs.GetTable("dbo", "ParentTable1");
            var p2 = dbs.GetTable("dbo", "ParentTable2");
            var c1 = dbs.GetTable("dbo", "ChildTable1");
            var c2 = dbs.GetTable("dbo", "ChildTable2");

            Assert.IsNotNull(p1);
            Assert.IsNotNull(p2);
            Assert.IsNotNull(c1);
            Assert.IsNotNull(c2);

            Assert.AreEqual<int>(1, p1.PrimaryKeys.Count);
            Assert.AreEqual<int>(0, p1.ForeignKeys.Count);
            Assert.AreEqual<string>("PK_PrimaryTable", p1.PrimaryKeys[0].Name);
            Assert.AreEqual<string>("PK_PrimaryTable2", p2.PrimaryKeys[0].Name);

            Assert.AreEqual<int>(2, c1.ForeignKeys.Count);
            Assert.AreEqual<int>(1, c2.ForeignKeys.Count);
            Assert.AreEqual<string>("FK_ChildTable1_PrimaryTable1", c1.ForeignKeys[0].Name);
            Assert.AreEqual<string>("FK_ChildTable1_PrimaryTable2", c1.ForeignKeys[1].Name);

            Assert.AreEqual<string>(p1.Name, c1.ForeignKeys[0].PrimaryTable.Name);
            Assert.AreEqual<string>(p2.Name, c1.ForeignKeys[1].PrimaryTable.Name);

            Assert.AreEqual<string>(p1.Name, c2.ForeignKeys[0].PrimaryTable.Name);
            Assert.AreEqual<string>(p1.PrimaryKeys[0].KeyColumns[0].Name, c1.ForeignKeys[0].KeyColumns[0].PrimaryTableColumnName);

            Assert.AreEqual<string>(p2.PrimaryKeys[0].KeyColumns[0].Name, c1.ForeignKeys[1].KeyColumns[0].PrimaryTableColumnName);
            Assert.AreEqual<string>(p2.PrimaryKeys[0].KeyColumns[1].Name, c1.ForeignKeys[1].KeyColumns[1].PrimaryTableColumnName);

            Assert.AreEqual<int>(2, c1.ParentTables.Count);
            Assert.AreEqual<string>("ParentTable1", c1.ParentTables[0].Name);
            Assert.AreEqual<string>("ParentTable2", c1.ParentTables[1].Name);

            Assert.AreEqual<int>(2, p1.ChildTables.Count);
            Assert.AreEqual<string>("ChildTable1", p1.ChildTables[0].Name);
            Assert.AreEqual<string>("ChildTable2", p1.ChildTables[1].Name);
        }

        [TestMethod]
        public void TestColumns()
        {
            var c1 = dbs.GetTable("dbo", "ChildTable1");
            Assert.AreEqual<bool>(true, c1.GetColumn("ChildTable").IsPrimaryKeyMember);
            Assert.AreEqual<bool>(false, c1.GetColumn("ChildTable").AllowDBNull);
            Assert.AreEqual<bool>(true, c1.GetColumn("Referent1").IsForeignKeyMember);
            Assert.AreEqual<bool>(true, c1.GetColumn("Referent21").IsForeignKeyMember);
            Assert.AreEqual<bool>(true, c1.GetColumn("Referent22").IsForeignKeyMember);
            Assert.AreEqual<string>("nvarchar", c1.GetColumn("Referent21").NativeType);
            Assert.AreEqual<int>(50, c1.GetColumn("Referent21").Size.Value);
            Assert.AreEqual<int>(3, c1.GetColumn("Referent21").Order);
            Assert.AreEqual<string>("[Referent21]", c1.GetColumn("Referent21").SafeName);
            Assert.AreEqual<string>("@Referent21", c1.GetColumn("Referent21").ParameteredName);
            Assert.AreEqual<string>("@Original_Referent21", c1.GetColumn("Referent21").OriginalParameteredName);
            Assert.AreEqual<string>("引用表21", c1.GetColumn("Referent21").Description);
            Assert.AreEqual<string>("引用表22", c1.GetColumn("Referent22").Description);
        }

        [TestMethod]
        public void TestViews()
        {
            var vw = dbs.GetView("dbo", "vw_P1C1");
            Assert.IsNotNull(vw);
            Assert.AreEqual<int>(2, vw.Columns.Count);
            Assert.AreEqual<int>(1, vw.GetColumn("parent_key").Order);
            Assert.AreEqual<Type>(typeof(Guid), vw.GetColumn("parent_key").SystemType);
            Assert.AreEqual<string>("[dbo].[vw_P1C1]", vw.FullName);
        }

        [TestMethod]
        public void TestStoreProcedures()
        {
            var sp1 = dbs.GetStoredProcedure("dbo", "SP1");
            Assert.IsNotNull(sp1);
            Assert.AreEqual<int>(2, sp1.Parameters.Count);
            Assert.AreEqual<int>(2, sp1.InputParameters.Count());
            Assert.AreEqual<int>(1, sp1.OutputParameters.Count());
            Assert.IsNull(sp1.ReturnParameter);

            Assert.AreEqual<Type>(typeof(int), sp1.InputParameters.First().SystemType);
            Assert.AreEqual<Type>(typeof(string), sp1.OutputParameters.First().SystemType);
            Assert.AreEqual<int>(1, sp1.GetParameter("@para1").Order);
        }

        [TestMethod]
        public void TestFunctions()
        {
            var func1 = dbs.GetFunction("dbo", "func1");
            Assert.IsNotNull(func1);
            Assert.AreEqual<int>(3, func1.Parameters.Count);
            Assert.AreEqual<int>(2, func1.InputParameters.Count());
            Assert.AreEqual<int>(0, func1.OutputParameters.Count());
            Assert.IsNotNull(func1.ReturnParameter);

            Assert.AreEqual<Type>(typeof(int), func1.InputParameters.First().SystemType);
            Assert.AreEqual<Type>(typeof(string), func1.InputParameters.Last().SystemType);
            Assert.AreEqual<Type>(typeof(string), func1.ReturnParameter.SystemType);
            Assert.AreEqual<int>(1, func1.GetParameter("@para1").Order);
            Assert.AreEqual<int>(2, func1.GetParameter("@para2").Order);
        }
    }
}
